Replicating tables that do not have a primary key
This functionality is supported only for Microsoft SQL Server Enterprise edition.
By default, Qlik Replicate automatically sets up MS-REPLICATION for each of the source tables in a replication task. However, MS-REPLICATION requires each of the source tables to have a primary key, which may not always be the case. Therefore, if you need to replicate tables that do not have a primary key, the following options are available:
Use MS-CDC
To set up MS-CDC, you first need to enable MS-CDC for the database by running the following command:
use [DBname]
EXEC sys.sp_cdc_enable_db
Then you need to enable MS-CDC for each of the source tables by running the following command:
EXECUTE sys.sp_cdc_enable_table @source_schema = N'MySchema', @source_name = N'MyTable', @role_name = NULL;
Replicating tables that do not have a Primary Key or a Unique Index may adversely affect performance (since additional database resources are required to capture the changes). However, you can prevent performance issues related to the absence of Primary Keys or a Unique Index by manually adding indexes to the target tables.
For more information on setting up MS-CDC for specific tables, please refer to the Microsoft website.
Do not use MS-Replication or MS-CDC
If your database is not set up for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but bear in mind that in such a setup only INSERT/DELETE DML events will be captured. UPDATE events will be ignored.
It is also important to note that a DELETE statement executed on an UPDATED source record, will not be applied on the target.